import sys

def get_root_folder():
    if sys.platform == "win32":
        return 'C:\\'
    else:
        return "/"
    
def setup_browser(proxyval = None):
    '''
    Return a mechanized browser.
    '''
    import mechanize
    import sys
    import cookielib
    USER_AGENT = "Mozilla/5.0 (X11; U; Linux i686; tr-TR; rv:1.8.1.9) Gecko/20071102 Pardus/2007 Firefox/2.0.0.9"
    mech = mechanize.Browser()
    if proxyval:
        mech.set_proxies({"http": proxyval})
    cj = cookielib.LWPCookieJar()
    mech.set_cookiejar(cj)
    # Browser options
    mech.set_handle_equiv(True)
    mech.set_handle_gzip(True)
    mech.set_handle_redirect(True)
    mech.set_handle_referer(True)
    mech.set_handle_robots(False)
    # Follows refresh 0 but not hangs on refresh > 0
    mech.set_handle_refresh(mechanize._http.HTTPRefreshProcessor(), max_time=5)
    mech.addheaders = [('User-agent', 'Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.1) Gecko/2008071615 Fedora/3.0.1-1.fc9 Firefox/3.0.1')]
    # mech.add_headers = [("User-agent", USER_AGENT)]
    # mech.add_headers = [("Pragma", PRAGMA)]
    # mech.add_headers = [("Cache-Control", CACHE_CONTROL)]
    return mech


def table_as_array(tableSoup, row_num='all', row_code = 'tr', col_code = 'td'):
    '''
    Given a soup for the table, return the web table or an individual row as an array.
    Each individual element in the output will be an array containing the different components
    '''
    output_array = []
    try:
        length_of_each_inner_array =  len(tableSoup.findAll(row_code))
        number_of_arrays = len(tableSoup.find(row_code).findAll(col_code))
        row_loc = []
        if row_num == 'all':
            row_loc = tableSoup.findAll(row_code)
        else:
            row_loc = [tableSoup.findAll(row_code)[row_num]]
        for i in row_loc:
            tds = i.findAll(col_code)
            for j in tds:
                for tag in j.findAll('a'):
                    tag.replaceWithChildren()
                for tag in j.findAll('b'):
                    tag.replaceWithChildren()
                for tag in j.findAll('br'):
                    tag.replaceWithChildren()
                for tag in j.findAll('font'):
                    tag.replaceWithChildren()
                for tag in j.findAll('p'):
                    tag.replaceWithChildren()
                for tag in j.findAll('sup'):
                    tag.replaceWithChildren()
                for tag in j.findAll('hr'):
                    tag.replaceWithChildren()
            tds = [x.contents for x in tds if x]
            if tds != []:
                output_array += [tds]
        try:
            if 'United States' in output_array[0][1]:
                return [[['No data available for this combination'],[' '],[' ']]]
            else:
                return output_array
        except:
            return output_array
    except:
        return [[['No data available for this combination'],[' '],[' ']]]

def write_array_to_excel(sheet_name, arrayObjects, arrayTitle = 'Awaiting Title', second_line = 'Awaiting here'):
    '''
    A function to basically write the array returned by the different retrievers to an excel sheet.
    The requirement is that at this time, the retrievers use common_tools.table_as_array to obtain the arrays.
    In Xp, this will have to be changed to a COM interface to allow autoupdating, if necessary
    '''
    from tempfile import TemporaryFile
    from xlwt import Workbook
    book = Workbook()
    sheet1 = book.add_sheet(str(sheet_name))
    total_number_of_rows = len(arrayObjects)
    total_number_of_columns = len(arrayObjects[0])
    sheet1.write(0,0, arrayTitle)
    sheet1.write(1,0, second_line)
    # For every value, if it exists, add it to data
    for i in range(total_number_of_rows):
        for j in range(total_number_of_columns):
            data = ''
            if len(arrayObjects[i][j]) > 1:
                data = ", ".join(arrayObjects[i][j])
            elif len(arrayObjects[i][j]) == 1:
                data = str(arrayObjects[i][j][0])
            sheet1.write(i+2,j,data)
    book.save('temp/test.xls')
    book.save(TemporaryFile())


def get_state_name_from_abbr(abbr):
    '''
    Given a state abbreviation, get the full name for the state
    '''
    state_abbrs = {"AL" : "ALABAMA", "AK" : "ALASKA", "AS" : "AMERICAN SAMOA", "AZ" : "ARIZONA", "AR" : "ARKANSAS", "CA" : "CALIFORNIA", "CO" : "COLORADO", "CT" : "CONNECTICUT", "DE" : "DELAWARE", "DC" : "DISTRICT OF COLUMBIA", "FM" : "FEDERATED STATES OF MICRONESIA", "FL" : "FLORIDA", "GA" : "GEORGIA", "GU" : "GUAM", "HI" : "HAWAII", "ID" : "IDAHO", "IL" : "ILLINOIS", "IN" : "INDIANA", "IA" : "IOWA", "KS" : "KANSAS", "KY" : "KENTUCKY", "LA" : "LOUISIANA", "ME" : "MAINE", "MH" : "MARSHALL ISLANDS", "MD" : "MARYLAND", "MA" : "MASSACHUSETTS", "MI" : "MICHIGAN", "MN" : "MINNESOTA", "MS" : "MISSISSIPPI", "MO" : "MISSOURI", "MT" : "MONTANA", "NE" : "NEBRASKA", "NV" : "NEVADA", "NH" : "NEW HAMPSHIRE", "NJ" : "NEW JERSEY", "NM" : "NEW MEXICO", "NY" : "NEW YORK", "NC" : "NORTH CAROLINA", "ND" : "NORTH DAKOTA", "MP" : "NORTHERN MARIANA ISLANDS", "OH" : "OHIO", "OK" : "OKLAHOMA", "OR" : "OREGON", "PW" : "PALAU", "PA" : "PENNSYLVANIA", "PR" : "PUERTO RICO", "RI" : "RHODE ISLAND", "SC" : "SOUTH CAROLINA", "SD" : "SOUTH DAKOTA", "TN" : "TENNESSEE", "TX" : "TEXAS", "UT" : "UTAH", "VT" : "VERMONT", "VI" : "VIRGIN ISLANDS", "VA" : "VIRGINIA", "WA" : "WASHINGTON", "WV" : "WEST VIRGINIA", "WI" : "WISCONSIN", "WY" : "WYOMING"}
    return state_abbrs[abbr].title()

def get_state_abbr_from_name(name):
    '''
    Opposite of the previous one.
    '''
    state_names = {"ALABAMA" : "AL", "ALASKA" : "AK", "AMERICAN SAMOA" : "AS", "ARIZONA" : "AZ", "ARKANSAS" : "AR", "CALIFORNIA" : "CA", "COLORADO" : "CO", "CONNECTICUT" : "CT", "DELAWARE" : "DE", "DISTRICT OF COLUMBIA" : "DC", "FEDERATED STATES OF MICRONESIA" : "FM", "FLORIDA" : "FL", "GEORGIA" : "GA", "GUAM" : "GU", "HAWAII" : "HI", "IDAHO" : "ID", "ILLINOIS" : "IL", "INDIANA" : "IN", "IOWA" : "IA", "KANSAS" : "KS", "KENTUCKY" : "KY", "LOUISIANA" : "LA", "MAINE" : "ME", "MARSHALL ISLANDS" : "MH", "MARYLAND" : "MD", "MASSACHUSETTS" : "MA", "MICHIGAN" : "MI", "MINNESOTA" : "MN", "MISSISSIPPI" : "MS", "MISSOURI" : "MO", "MONTANA" : "MT", "NEBRASKA" : "NE", "NEVADA" : "NV", "NEW HAMPSHIRE" : "NH", "NEW JERSEY" : "NJ", "NEW MEXICO" : "NM", "NEW YORK" : "NY", "NORTH CAROLINA" : "NC", "NORTH DAKOTA" : "ND", "NORTHERN MARIANA ISLANDS" : "MP", "OHIO" : "OH", "OKLAHOMA" : "OK", "OREGON" : "OR", "PALAU" : "PW", "PENNSYLVANIA" : "PA", "PUERTO RICO" : "PR", "RHODE ISLAND" : "RI", "SOUTH CAROLINA" : "SC", "SOUTH DAKOTA" : "SD", "TENNESSEE" : "TN", "TEXAS" : "TX", "UTAH" : "UT", "VERMONT" : "VT", "VIRGIN ISLANDS" : "VI", "VIRGINIA" : "VA", "WASHINGTON" : "WA", "WEST VIRGINIA" : "WV", "WISCONSIN" : "WI", "WYOMING" : "WY"}
    if name in state_names:
        return state_names[name]
    else:
        return name

def make_sure_of_equisize_columns(arr):
    '''
    Before outputting to excel, make sure that all the columns are the same size.
    '''
    try:
        len_array = [len(row) for row in arr]
        max_col_num = max(len_array)
        for row in arr:
            while len(row) < max_col_num:
                row += [' ']
        return arr
    except:
        return [[['No data available for this combination'],[' '],[' ']]]

def write_dict_to_excel(outputDict, filename = 'temp/test.xls'):
    '''
    A function to basically write the array returned by the different retrievers to an excel sheet.
    The requirement is that at this time, the retrievers use common_tools.table_as_array to obtain the arrays.
    In Xp, this will have to be changed to a COM interface to allow autoupdating, if necessary
    '''
    from tempfile import TemporaryFile
    from xlwt import Workbook
    tabCounter = 0
    book = Workbook()
    tabNames = outputDict.keys()
    tabNames.sort()
    for tabName in tabNames:
        # Since excel tabs can only be 31 characters,
        # it is necessary to trim some names
        try:
            state_name = tabName.split(' - ')[0]
            state_abbr = get_state_abbr_from_name(state_name.upper())
            modtabName = tabName.replace(state_name, state_abbr)
        except:
            modtabName = tabName

        modtabName = modtabName.replace("Congressional", "Cong.")
        modtabName = modtabName.replace("District", "Distr.")
        modtabName = modtabName.replace("School", "Sch.")
        if len(modtabName) > 30: modtabName = 'sheet%s' % (tabCounter)
        tabCounter += 1
        sheet1 = book.add_sheet(str(modtabName))
        titleNames = outputDict[tabName].keys()
        titleNames.sort()
        column_progression = 0
        for title in titleNames:
            arrayObjects = make_sure_of_equisize_columns(outputDict[tabName][title])
            total_number_of_rows = len(arrayObjects)
            total_number_of_columns = len(arrayObjects[0])
            title = title.split(' - ')
            sheet1.write(0,column_progression, title[0])
            if len(title) > 1:
                geoname = ' - '.join(title[1:])
                sheet1.write(1,column_progression, geoname)
            tabNameSplit = tabName.split(' - ')
            sheet1.write(2,column_progression, tabNameSplit[0])
            if len(tabNameSplit) > 1:
                geoname = ' - '.join(tabNameSplit[1:])
                sheet1.write(3,column_progression, geoname)

            # For every value, if it exists, add it to data
            for i in range(total_number_of_rows):
                for j in range(total_number_of_columns):
                    data = ''
                    if len(arrayObjects[i][j]) > 1:
                        data = ", ".join(arrayObjects[i][j])
                    elif len(arrayObjects[i][j]) == 1:
                        data = str(arrayObjects[i][j][0])
                        data = data.replace('&nbsp;', '')
                    sheet1.write(i+5,j + column_progression,data)
            book.save(filename)
            book.save(TemporaryFile())
            column_progression += (total_number_of_columns + 1)            
    
def isonline(reliableserver='http://www.google.com'):
    """Returns True if we appear to have an internet connection or False.
    It defaults to using google as a test server, but you can supply an alternative if you want."""
    from urllib2 import urlopen
    try:
        urlopen(reliableserver)
        return True
    except IOError:
        try:
            urlopen('http://www.yahoo.com')
            return True
        except IOError:
            return False

def updates_available(testSite):
    from urllib2 import urlopen
    try:
        x = urlopen(testSite)
        if x.geturl() == testSite:
            return True
    except IOError:
        return False
    
def fix_fdic_files():
    '''
    FDIC files are given in the csv format. With some preprocessing to correctly order
    the first five columns, this function reads the institution codes and gives a new excel sheet with relevant
    details.
    '''
    from xlrd import open_workbook
    from tempfile import TemporaryFile
    from xlwt import Workbook
    book = Workbook()
    sheet1 = book.add_sheet('Payday Lenders')
    import google_maps_retriever
    wb = open_workbook('/home/ajith/Documents/Write/Programming/Kml/python/OnlineDataRetriever2/config/business_lists/FDIC definitions.xls')
    defins = {}
    for s in wb.sheets():
        if s.name == 'Sheet1':
            try:
                for row in range(1,s.nrows):
                    defins[s.cell(row,0).value] = s.cell(row,1).value
            except:
                pass
    wb = open_workbook('/home/ajith/Documents/Write/Programming/Kml/python/OnlineDataRetriever2/config/business_lists/FDIC Institutions.xls')
    for s in wb.sheets():
        if s.name == 'Sheet1':
            for col in range(6,s.ncols):
                codeval = s.cell(0,col).value
                if codeval in defins:
                    sheet1.write(0,col,defins[codeval])
                else:
                    sheet1.write(0,col,codeval)
    book.save('/home/ajith/Documents/Write/Programming/Kml/python/OnlineDataRetriever2/config/business_lists/FDIC temp.xls')
    book.save(TemporaryFile())
        

def get_credit_union_data():
    '''
    Credit union data is updated every yeear by the NCUA credit union website.
    These files are usually available in ascii format.
    Running this code will convert these ascii files to an excel sheet that can then
    be modified according to the rules used for other files.
    Run this modifying the file until there is not output that says indices with .. values
    
    '''
    from xlrd import open_workbook
    from tempfile import TemporaryFile
    from xlwt import Workbook
    book = Workbook()
    sheet1 = book.add_sheet('Sheet1')
    filename = '/home/ajith/Documents/Write/Programming/Kml/python/OnlineDataRetriever2/config/business_lists/2009 CuDirectory ASCII.TXT'
    x = open(filename).readlines()
    x = [y.strip() for y in x]
    x = [y.replace(',",', '",') for y in x]
    x = [y.replace('"," ', '","') for y in x]
    x = [y.replace('","', ',') for y in x]
    x = [y.replace(', ', ' ') for y in x]
    x = [y.replace('"', '') for y in x]
    x = [y.replace("'", "") for y in x]
    x = [y.split(',') for y in x]
    for z,i in enumerate(x):
        if len(i) < len(x[0]): print "Indices with less values: %s" % z
    for z,i in enumerate(x):
        if len(i) > len(x[0]): print "Indices with more values: %s" % z
    for z,i in enumerate(x):
        for y,j in enumerate(i):
            sheet1.write(z,y,j)
    book.save('/home/ajith/Documents/Write/Programming/Kml/python/OnlineDataRetriever2/config/business_lists/temp.xls')
    book.save(TemporaryFile())

def write_business_dict_to_excel(outputDict, filename = 'temp/test.xls'):
    '''
    A function to basically write the array returned by the different retrievers to an excel sheet.
    The requirement is that at this time, the retrievers use common_tools.table_as_array to obtain the arrays.
    In Xp, this will have to be changed to a COM interface to allow autoupdating, if necessary
    '''
    from tempfile import TemporaryFile
    from xlwt import Workbook
    tabCounter = 0
    book = Workbook()
    tabNames = outputDict.keys()
    tabNames.sort()
    for tabName in tabNames:
        # Since excel tabs can only be 31 characters,
        # it is necessary to trim some names
        try:
            state_name = tabName.split(' - ')[0]
            state_abbr = get_state_abbr_from_name(state_name.upper())
            modtabName = tabName.replace(state_name, state_abbr)
        except:
            modtabName = tabName
        modtabName = modtabName.replace("Congressional", "Cong.")
        modtabName = modtabName.replace("District", "Distr.")
        modtabName = modtabName.replace("School", "Sch.")
        if len(modtabName) > 30: modtabName = 'sheet%s' % (tabCounter)
        tabCounter += 1
        sheet1 = book.add_sheet(str(modtabName))
        titleNames = outputDict[tabName].keys()
        titleNames.sort()
        column_progression = 0
        for title in titleNames:
            arrayObjects = make_sure_of_equisize_columns(outputDict[tabName][title])
            total_number_of_rows = len(arrayObjects)
            total_number_of_columns = len(arrayObjects[0])
            title = title.split(' - ')
            sheet1.write(0,column_progression, title[0])
            if len(title) > 1:
                sheet1.write(1,column_progression, title[1])
            tabNameSplit = tabName.split(' - ')
            sheet1.write(2,column_progression, tabNameSplit[0])
            if len(tabNameSplit) > 1:
                sheet1.write(3,column_progression, tabNameSplit[1])

            # For every value, if it exists, add it to data
            for i in range(total_number_of_rows):
                for j in range(total_number_of_columns):
                    data = ''
                    if len(arrayObjects[i][j]) > 1:
                        data = ", ".join(arrayObjects[i][j])
                    elif len(arrayObjects[i][j]) == 1:
                        data = str(arrayObjects[i][j][0])
                        data = data.replace('&nbsp;', '')
                    sheet1.write(i+5,j + column_progression,data)
            book.save(filename)
            book.save(TemporaryFile())
            column_progression += (total_number_of_columns + 1)

def creating_metas():
    ''' Given the table files, create metas
    '''
    import glob
    for tableFile in glob.glob(os.path.join('config/tables', 'MEPS * tables')):
        baseFilename = os.path.basename(tableFile)
        metaFilename = baseFilename.replace('tables', 'meta')
        baseFilename = baseFilename.replace(' - tables', '')
        line1 = "DbName\t%s" % baseFilename
        line2 = "GeoCodeName\tNone"
        line3 = "AvailableGeoBoundaries\tMSA, State"
        output_string = "%s\n%s\n%s" % (line1, line2, line3)
        f = open('config/metas/%s' % metaFilename, 'w')
        print baseFilename
        f.write(output_string)
        f.close()

def switching_tabs():
    ''' Given the table files, create metas
    '''
    import glob
    for tableFile in glob.glob(os.path.join('config/tables', 'MEPS * tables')):
        output_string = ''
        f = open(tableFile, 'r')
        for line in f:
            data = line.split('\t')
            part1, part2 = data[0].strip(), data[1].strip()
            output_string += ('%s\t%s\n' % (part2, part1))
        f.close()
        f = open(tableFile, 'w')
        f.write(output_string)
        f.close()

def adding_dbName():
    import glob
    for tableFile in glob.glob(os.path.join('config/tables', 'MEPS * tables')):
        baseFilename = os.path.basename(tableFile)
        baseFilename = baseFilename.replace(' - tables', '')
        line1 = "DbName\t%s" % baseFilename
        actual_string = open(tableFile, 'r').readlines()
        new_string = "%s\n%s" % (line1, actual_string)
        f = open(tableFile, 'w')
        f.write(new_string)
        f.close()

def remove_all_compiled_files():
    import os
    import glob
    for tableFile in glob.glob(os.path.join('', '*.pyc')):
        baseFilename = os.path.basename(tableFile)
        print baseFilename
        try:
            os.system('rm %s' % tableFile)
        except:
            os.system('del %s' % tableFile)
    for tableFile in glob.glob(os.path.join('*/', '*.pyc')):
        baseFilename = os.path.basename(tableFile)
        print baseFilename
        try:
            os.system('rm %s' % tableFile)
        except:
            os.system('del %s' % tableFile)

def open_excelsheet(filename):
    from win32com.client import constants, Dispatch
    import pythoncom
    import os
    xcl = Dispatch("Excel.Application")
    xcl.Workbooks.Open(filename)
    xcl.Visible = True
    
def fix_file():
    import glob, os
    total_count = 0
    for tableFile in glob.glob(os.path.join('/home/ajith/Documents/Write/Programming/Kml/python/OnlineDataRetriever2/config/tables', '* - tables')):
        x = open(tableFile).readlines()
        total_count += (len(x)-1)
    print total_count

if __name__ == '__main__':
    print isonline()
    #remove_all_compiled_files()
    print fix_file()


